﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using com.code.Editors.Code;

namespace com.Editors
{
    public class SchemaDAL
    {
        string conStr = "";
        public SchemaDAL(string conStr)
        {
            this.conStr = conStr;
        }

        public ArrayList GetTables()
        {
            ArrayList list = new ArrayList();
            //string str = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "p_UtilGetTableNames";
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new GuidDataTable(reader.GetString(0)));
                }
            }
            catch (SqlException e) { }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return list;
        }

        public ArrayList GetUserSPs()
        {
            ArrayList list = new ArrayList();
            //string str = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "dbo.p_UtilGetUserSPs";
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader.GetString(0));
                }
            }
            catch (SqlException e) { }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return list;
        }

        public ArrayList GetTableColumns(string table)
        {
            DataSet ds = new DataSet();

            ArrayList listCols = new ArrayList();
            //string str = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "p_UtilGetTableData";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@tableName", table);
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            try
            {
                //adapt.FillSchema(ds, table);

                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    GuidDataColumn colObj = new GuidDataColumn(reader.GetString(3));
                    colObj.ColType = reader.GetString(5);
                    colObj.MaxLength  = reader.GetInt32(7);
                    listCols.Add(colObj);
                    //listCols.Add(reader.GetString(3));

                    //listCols.Add(Table(reader.GetString(3), reader.GetString(5), reader.GetInt32(6)));
                }
            }
            catch (SqlException e) { }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return listCols;
        }

        public string GetStoredProcedureColumns(string spName)
        {

            string data = "";
            //string str = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "sp_helptext";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@objname", spName);

            try
            {

                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    data += reader.GetString(0);
                }
            }
            catch (SqlException e) { }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return data;
        }

        public DataSet GetStoredProcedureColumnsSchema(string spName)
        {
            DataSet ds = new DataSet();
            string data = "";
            //string str = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;
            //cmd.Parameters.Add("@objname", spName);
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            try
            {

                adapt.FillSchema(ds, SchemaType.Source);
            }
            catch (SqlException e) { }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return ds;
        }
    }
}
